import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.naming.spi.DirStateFactory.Result;


public class FriendManagement {
	
	// Connection object field
	private static Connection con;
	
	// Connection object created when the FriendManagement is created
	public FriendManagement(){
		// Connection object get from the getConnection method
		con = ConnectionManagement.getConnection();
	}
	
	// Get the all friends methods
	public ResultSet getAllFriends(){
		try{
			ResultSet rs =null;
			Statement stmt =null;
			stmt = con.createStatement();
			rs = stmt.executeQuery("SELECT * FROM FRIENDS ORDER BY ID DESC");
			return rs;
		}catch(SQLException ex){
			System.err.println(ex.getMessage());
			return null;
		}
	}
	
	// Get a friend method by id
	public Friend getFriend(int id){
		
		ResultSet rs = null;
		try(
			PreparedStatement pstmt = con.prepareStatement("SELECT * FROM FRIENDS WHERE ID=?");
		){
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			if(rs.next()){
				return new Friend(id,rs.getString(2),rs.getString(3),rs.getString(4));
			}else{
				return null;
			}
		}catch(SQLException ex){
			System.err.println(ex.getMessage());
			return null;
		}finally{
			if(rs!=null){
				try {
					rs.close();
				} catch (SQLException ex) {
					System.err.println(ex.getMessage());
				}
			}
		}
	}
	
	// insert new friend into the database
	public boolean insert(Friend friend){
		
		try(
			PreparedStatement pstmt = con.prepareStatement("INSERT INTO FRIENDS VALUES(FRIENDS_SEQ.NEXTVAL,?,?,?)");
		){
			pstmt.setString(1, friend.getName());
			pstmt.setString(2, friend.getGender());
			pstmt.setString(3, friend.getAddress());
			pstmt.executeUpdate();
			return true;
		}catch(SQLException ex){
			System.err.println(ex.getMessage());
		}
		return false;
	}
	
	// updating the existing friend 
	public boolean update(Friend newFriend){
		
		try(
			PreparedStatement pstmt = con.prepareStatement("UPDATE FRIENDS SET Name=?, Gender=?, Address=? WHERE ID=?");
		){
			pstmt.setString(1, newFriend.getName());
			pstmt.setString(2, newFriend.getGender());
			pstmt.setString(3, newFriend.getAddress());
			pstmt.setInt(4,newFriend.getId());
			int updated = pstmt.executeUpdate();
			if(updated==1){
				return true;
			}else{
				return false;
			}			
		}catch(SQLException ex){
			System.err.println(ex.getMessage());
			return false;
		}
	}
	
	// delete the existing friend by id
	public boolean delete(int id){
		
		try(
			PreparedStatement pstmt = con.prepareStatement("DELETE FROM FRIENDS WHERE ID=?");
		){
			pstmt.setInt(1, id);
			int deleted = pstmt.executeUpdate();
			if(deleted==1){
				return true;
			}else{
				return false;
			}
		}catch(SQLException ex){
			System.err.println(ex.getMessage());
			return false;
		}
	}
	
	// Search friend by name
	public ResultSet searchByName(String name){

		try{
			ResultSet rs = null;
			Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
			rs =stmt.executeQuery("SELECT * FROM FRIENDS WHERE LOWER(Name) LIKE LOWER("+"%"+name+"%"+")");
			return rs;
		}catch(SQLException ex){
			System.err.println(ex.getMessage());
			return null;
		}
	}
}
